﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupDifferential]
#-- Purpose:		Performs a differential backup of a database.  Chooses between
#--					LiteSpeed if available, otherwise standard Backup
#--	Last Update:	01/30/2013
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	master.dbo.xp_backup_database
#-- Called By:		[dbo].[spBackupDifferentialAll]
#--					Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupDifferential]
(
	@database_name	sysname,
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL,
	@ignore_skip	bit					= 0
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@dir varchar(8000), @sCommand varchar(8000), @file_name varchar(8000),
			@readonly bit, @offline bit, @isEndOfMonth bit, @hasFullBackup bit,
			@current_date datetime, @job_name_filter varchar(125),
			@do_full_instead bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupDifferential'

--- Ensure the database skips backup if configured not to backup
IF ISNULL(@ignore_skip, 0) = 0 
	AND EXISTS (SELECT TOP 1 database_name FROM dbo.vwDatabase_DoNotBackup WHERE database_name = @database_name)
  BEGIN
	RETURN
  END

--- @database_name cannot be tempdb
SELECT	@database_name = LTRIM(RTRIM(ISNULL(@database_name, '')))
IF @database_name = 'tempdb'
  BEGIN
	PRINT 'tempdb database cannot be backed up (' + @database_name + ')'
	RETURN
  END

--- Don't do anything if the database is not online
EXEC	@offline = dbo.spIsDatabaseOffline @database_name
IF @offline = 1
  BEGIN
	SELECT @sCommand = 'The database [' + @database_name + '] is not online'
	RAISERROR (@sCommand, 0, 0) WITH NOWAIT
	RETURN
  END

--- @database_name is a mandatory fields
IF @database_name = ''
  BEGIN
	RAISERROR ('The name of the database to backup is required (@database_name)', 16, 1)
	RETURN
  END

--- @database_name must be an existing database
IF NOT EXISTS (SELECT TOP 1 name FROM master.sys.databases WHERE name = @database_name)
  BEGIN
	RAISERROR ('The requested database does not exist', 16, 1)
	RETURN
  END

--- Snapshot databases cannot be backed up
IF EXISTS (SELECT TOP 1 name FROM master.sys.databases WHERE name = @database_name AND source_database_id IS NOT NULL)
  BEGIN
	PRINT 'Snapshot databases cannot be backed up (' + @database_name + ')'
	RETURN
  END

ELSE 
  BEGIN
	BEGIN TRY
		--- Determine if a differential will be large enough that a full backup should be run instead
		EXEC	@do_full_instead = spBackupShouldFullInsteadOfDiff @database_name = @database_name
		IF @do_full_instead = 1
		  BEGIN
			EXEC dbo.spBackupFull @database_name = @database_name, @verify = @verify, @label = @label
		  END
		ELSE
		  BEGIN
			--- Get Current date/time stamp and set base job name
			SELECT	@current_date = GETDATE(),
					@job_name_filter = 'DBA: Backup Differential'
	
			--- Get database settings
			EXEC	@readonly = dbo.spIsDatabaseReadOnly @database_name
			EXEC	@isEndOfMonth = dbo.spIsEndOfMonth
			EXEC	@hasFullBackup = dbo.spHasFullBackup @database_name
			EXEC	@offline = dbo.spIsDatabaseOffline @database_name

			--- Determine if there is a label for the job execution
			IF @label IS NULL
				SELECT		@label = REPLACE(ctm_label, '''', '')
				FROM		dbo.tblControlmExecution (READUNCOMMITTED)
				WHERE		ctm_job_name LIKE (@job_name_filter + '%')
							AND ctm_date_started <= @current_date
				ORDER BY	ROW_NUMBER() OVER(ORDER BY ctm_job_name DESC)
	
			IF @offline = 0
			  BEGIN
				IF @database_name = 'master' OR @hasFullBackup = 0 -- OR @isEndOfMonth = 1
				  BEGIN
					EXEC	dbo.spBackupFull @database_name = @database_name, @verify = @verify, @label = @label
				  END
				ELSE IF @readonly = 0
				  BEGIN
					--- Ensure the directory exists  (xp)
					EXEC	dbo.spGetBackupDirectory @database_name = @database_name, @backup_directory_name = @dir OUTPUT 
					EXEC	dbo.spCreateDirectory @dir

					--- Get File Name
					EXEC	dbo.spGetDifferentialBackupFileName @database_name = @database_name, @backup_directory_name = @file_name OUTPUT

					--- Build the command to backup the database
					IF EXISTS(select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[xp_backup_database]'))
					  BEGIN
						SELECT	@sCommand = 'EXEC master.dbo.xp_backup_database @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''', @with=''DIFFERENTIAL'''

						IF @label IS NOT NULL
							SELECT	@sCommand = @sCommand + ', @desc=''' + @label + ''''
					  END
					ELSE
					  BEGIN
						SELECT	@sCommand = 'BACKUP DATABASE [' + @database_name + '] TO DISK = ''' + @file_name + ''' WITH DIFFERENTIAL'

						--- Set the backup label
						IF @label IS NOT NULL
						  BEGIN
							SELECT	@sCommand = @sCommand + ', NAME = ''' + @label + ''''
						  END

						IF @verify = 1
						  BEGIN
							SELECT @sCommand = @sCommand + ', CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
						  END
					  END

					--- Execute the command (backup the database)
					IF LEN(@sCommand) > 0
					  BEGIN
						EXEC	(@sCommand)
						EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
					  END

					--- Also take a log backup to provide another recovery path to this point (all tlogs or diff)
					EXEC	dbo.spBackupLog @database_name = @database_name, @verify = @verify, @label = @label, @ignore_skip = @ignore_skip
				  END
			  END
		  END
	END TRY
	BEGIN CATCH
		--- Log the error - if possible
		DECLARE @sResults varchar(8000)
		SELECT @sResults = '[spBackupDifferential] The database [' + @database_name + '] failed to backup (' + @sCommand + ').'

		IF ERROR_NUMBER() IS NOT NULL
			SELECT @sResults = @sResults + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Insert the error in the database
		IF @sResults IS NOT NULL
		  BEGIN
			INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
				VALUES (@database_name, GETDATE(), 'backup_diff', 0, @sResults)

			--- Must raise an error so sqlcmd catches the severity level
			RAISERROR (@sResults, 16, 1)
			RETURN
		  END
		ELSE
		  BEGIN
			--- Must raise an error so sqlcmd catches the severity level
			SELECT @sResults = '[spBackupDifferential] The database [' + ISNULL(@database_name, '') + '] failed to backup (' + ISNULL(@sCommand, '') + ').'
			RAISERROR (@sResults, 16, 1) WITH LOG
			RETURN
		  END
	END CATCH
  END
  
SET NOCOUNT OFF
